const express = require('express');
const mysql = require('mysql2');
const path = require('path');
const fs = require('fs');
const cors = require('cors');
const app = express();
const port = 3000;

app.use(cors());
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'local1'
});

// 设置静态文件目录
app.use(express.static(path.join(__dirname, 'public')));

// 根路径返回index.html页面
app.get('/', (req, res) => {
    // test.js
  // console.log("Hello, Node.js!"); // 应该在控制台输出
    res.sendFile(path.join(__dirname, 'public', 'index.html'));
  });
  
app.use(express.json());

// 处理获取图片请求
app.get('/image/:id', (req, res) => {
  const imageId = parseInt(req.params.id, 10);
  pool.query('SELECT path FROM images WHERE id = ?', [imageId], (error, results) => {
    if (error) {
      console.error('Database query error:', error);
      return res.status(500).send('Database error');
    }
    
    if (results.length === 0) {
      return res.status(404).send('Image not found');
    }
    
    const imagePath = results[0].path;
    fs.access(imagePath, fs.constants.F_OK, (err) => {
      if (err) {
        return res.status(404).send('Image file not found');
      }
      res.sendFile(path.resolve(imagePath));
    });
  });
});

// 处理删除图片请求
app.delete('/image/:id', (req, res) => {
  const imageId = parseInt(req.params.id, 10);
  pool.query('SELECT path FROM images WHERE id = ?', [imageId], (error, results) => {
    if (error) {
      console.error('Database query error:', error);
      return res.status(500).send('Database error');
    }

    if (results.length === 0) {
      return res.status(404).send('Image not found');
    }

    const imagePath = results[0].path;

    // 删除文件
    fs.unlink(imagePath, (err) => {
      if (err) {
        console.error('Error deleting file:', err);
        return res.status(500).send('Error deleting file');
      }

      // 删除数据库记录
      pool.query('DELETE FROM images WHERE id = ?', [imageId], (error) => {
        if (error) {
          console.error('Database deletion error:', error);
          return res.status(500).send('Database error');
        }

        res.status(200).send('Image deleted successfully');
      });
    });
  });
});

// 获取图片总数
app.get('/images/count', (req, res) => {
  pool.query('SELECT COUNT(*) AS count FROM images', (error, results) => {
    if (error) {
      console.error('Database query error:', error);
      return res.status(500).send('Database error');
    }
    res.json({ count: results[0].count });
  });
});

app.listen(port, () => {
  console.log(`Server is running on http://localhost:${port}`);
});
